# -*- coding: utf-8 -*- 
# covert .csv file of stock histroy data into db

import os
from time import time
from sqlalchemy import *

def pline(line):
    l = line.strip().split(',')
    return {'date':l[0], 'open':l[1], 'high':l[2], 
            'low':l[3],'close':l[4],'volume':l[5]}

db = create_engine('sqlite:///stocks.db')
metadata = BoundMetaData(db)
gps_table = Table('gps', metadata, autoload=True)

os.chdir('hpss')

fs = os.listdir('.')
fs.sort()

for f in fs:
    start_at = time()

    c = f.split('.')[0]
    hp = []

    for l in open(f).readlines()[1:]:
        r = pline(l)
        r['code'] = c
        hp.append(r)
        
    gps_table.insert().execute(hp)
    
    end_at = time()
    print c, 'using %s seconds'%str(end_at - start_at)
